package com.imut.lagain.repository;

import com.imut.lagain.entity.FinalAnswerLock;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 最终答案之锁数据访问层
 */
@Mapper
public interface FinalAnswerLockRepository extends BaseMapper<FinalAnswerLock> {

    /**
     * 根据用户ID查询所有锁
     */
    List<FinalAnswerLock> findByUserIdAndDeletedAtIsNull(Long userId);

    /**
     * 根据用户ID和状态查询锁
     */
    List<FinalAnswerLock> findByUserIdAndStatusAndDeletedAtIsNull(Long userId, String status);

    /**
     * 根据用户ID和锁ID查询锁
     */
    Optional<FinalAnswerLock> findByIdAndUserIdAndDeletedAtIsNull(Long id, Long userId);

    /**
     * 根据分享令牌查询锁
     */
    Optional<FinalAnswerLock> findByShareTokenAndIsSharedTrueAndDeletedAtIsNull(String shareToken);

    /**
     * 查询即将到期的锁（解锁日期在指定天数内）
     */
    @Select("SELECT * FROM final_answer_lock WHERE user_id = #{userId} AND unlock_date BETWEEN #{startDate} AND #{endDate} AND lock_status = 'locked' AND deleted_at IS NULL")
    List<FinalAnswerLock> findUpcomingUnlocks(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 查询已过期但未解锁的锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE unlock_date < #{currentTime} AND lock_status = 'locked' AND deleted_at IS NULL")
    List<FinalAnswerLock> findExpiredLocks(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询被锁定的锁（尝试次数超限）
     */
    @Select("SELECT * FROM final_answer_lock WHERE is_locked = 1 AND lockout_until > #{currentTime} AND deleted_at IS NULL")
    List<FinalAnswerLock> findLockedOutLocks(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 根据用户ID和类别查询锁
     */
    List<FinalAnswerLock> findByUserIdAndCategoryAndDeletedAtIsNull(Long userId, String category);

    /**
     * 根据用户ID和标签查询锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE user_id = #{userId} AND tags LIKE CONCAT('%', #{tag}, '%') AND deleted_at IS NULL")
    List<FinalAnswerLock> findByUserIdAndTag(@Param("userId") Long userId, @Param("tag") String tag);

    /**
     * 根据用户ID和优先级查询锁
     */
    List<FinalAnswerLock> findByUserIdAndPriorityAndDeletedAtIsNull(Long userId, Integer priority);

    /**
     * 查询用户收藏的锁
     */
    List<FinalAnswerLock> findByUserIdAndIsFavoriteTrueAndDeletedAtIsNull(Long userId);

    /**
     * 根据用户ID和时间范围查询锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE user_id = #{userId} AND created_at BETWEEN #{startDate} AND #{endDate} AND deleted_at IS NULL")
    List<FinalAnswerLock> findByUserIdAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 统计用户锁的总数
     */
    @Select("SELECT COUNT(*) FROM final_answer_lock WHERE user_id = #{userId} AND deleted_at IS NULL")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户已解锁的锁数量
     */
    @Select("SELECT COUNT(*) FROM final_answer_lock WHERE user_id = #{userId} AND lock_status = 'unlocked' AND deleted_at IS NULL")
    Long countUnlockedByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各状态锁的数量
     */
    @Select("SELECT lock_status, COUNT(*) FROM final_answer_lock WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY lock_status")
    List<Object[]> countByUserIdGroupByStatus(@Param("userId") Long userId);

    /**
     * 统计用户各类别锁的数量
     */
    @Select("SELECT category, COUNT(*) FROM final_answer_lock WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY category")
    List<Object[]> countByUserIdGroupByCategory(@Param("userId") Long userId);

    /**
     * 查询用户最近创建的锁
     */
    List<FinalAnswerLock> findTop10ByUserIdAndDeletedAtIsNullOrderByCreatedAtDesc(Long userId);

    /**
     * 查询用户最近解锁的锁
     */
    List<FinalAnswerLock> findTop10ByUserIdAndLockStatusAndDeletedAtIsNullOrderByActualUnlockTimeDesc(Long userId, String lockStatus);

    /**
     * 查询用户最常查看的锁
     */
    List<FinalAnswerLock> findTop10ByUserIdAndDeletedAtIsNullOrderByViewCountDesc(Long userId);

    /**
     * 查询需要备份的锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE backup_enabled = 1 AND (last_backup_time IS NULL OR last_backup_time < #{backupThreshold}) AND deleted_at IS NULL")
    List<FinalAnswerLock> findLocksNeedingBackup(@Param("backupThreshold") LocalDateTime backupThreshold);

    /**
     * 查询需要发送提醒的锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE reminder_enabled = 1 AND reminder_time <= #{currentTime} AND lock_status = 'locked' AND deleted_at IS NULL")
    List<FinalAnswerLock> findLocksNeedingReminder(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询高风险锁（多次失败尝试）
     */
    @Select("SELECT * FROM final_answer_lock WHERE attempt_count >= #{threshold} AND lock_status = 'locked' AND deleted_at IS NULL")
    List<FinalAnswerLock> findHighRiskLocks(@Param("threshold") Integer threshold);

    /**
     * 查询长时间未访问的锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE last_view_time < #{threshold} AND deleted_at IS NULL")
    List<FinalAnswerLock> findInactiveLocks(@Param("threshold") LocalDateTime threshold);

    /**
     * 查询共享的锁
     */
    List<FinalAnswerLock> findByIsSharedTrueAndShareExpiresAtAfterAndDeletedAtIsNull(LocalDateTime currentTime);

    /**
     * 查询过期的共享锁
     */
    @Select("SELECT * FROM final_answer_lock WHERE is_shared = 1 AND share_expires_at <= #{currentTime} AND deleted_at IS NULL")
    List<FinalAnswerLock> findExpiredSharedLocks(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 根据内容类型统计锁数量
     */
    @Select("SELECT content_type, COUNT(*) FROM final_answer_lock WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY content_type")
    List<Object[]> countByUserIdGroupByContentType(@Param("userId") Long userId);

    /**
     * 查询用户的锁统计信息
     */
    @Select("SELECT COUNT(*) as total, SUM(CASE WHEN lock_status = 'unlocked' THEN 1 ELSE 0 END) as unlocked, SUM(CASE WHEN lock_status = 'locked' THEN 1 ELSE 0 END) as locked FROM final_answer_lock WHERE user_id = #{userId} AND deleted_at IS NULL")
    Map<String, Object> getUserLockStats(@Param("userId") Long userId);

    /**
     * 更新锁的查看次数
     */
    @Update("UPDATE final_answer_lock SET view_count = view_count + 1, last_viewed_at = #{viewTime} WHERE id = #{lockId}")
    void incrementViewCount(@Param("lockId") Long lockId, @Param("viewTime") LocalDateTime viewTime);

    /**
     * 更新锁的解锁尝试次数
     */
    @Update("UPDATE final_answer_lock SET unlock_attempts = unlock_attempts + 1, last_attempt_time = #{attemptTime} WHERE id = #{lockId}")
    void incrementUnlockAttempts(@Param("lockId") Long lockId, @Param("attemptTime") LocalDateTime attemptTime);

    /**
     * 重置锁的解锁尝试次数
     */
    @Update("UPDATE final_answer_lock SET unlock_attempts = 0, last_attempt_time = NULL WHERE id = #{lockId}")
    void resetUnlockAttempts(@Param("lockId") Long lockId);

    /**
     * 更新锁的备份时间
     */
    @Update("UPDATE final_answer_lock SET last_backup_at = #{backupTime} WHERE id = #{lockId}")
    void updateLastBackupTime(@Param("lockId") Long lockId, @Param("backupTime") LocalDateTime backupTime);

    /**
     * 软删除锁
     */
    @Update("UPDATE final_answer_lock SET deleted_at = #{deleteTime} WHERE id = #{lockId} AND user_id = #{userId}")
    void softDeleteLock(@Param("lockId") Long lockId, @Param("userId") Long userId, @Param("deleteTime") LocalDateTime deleteTime);

    /**
     * 恢复软删除的锁
     */
    @Update("UPDATE final_answer_lock SET deleted_at = NULL WHERE id = #{lockId} AND user_id = #{userId}")
    void restoreLock(@Param("lockId") Long lockId, @Param("userId") Long userId);

    /**
     * 更新锁的分享状态
     */
    @Update("UPDATE final_answer_lock SET is_shared = #{isShared}, share_token = #{shareToken}, share_expires_at = #{expiresAt} WHERE id = #{lockId} AND user_id = #{userId}")
    void updateShareStatus(@Param("lockId") Long lockId, @Param("userId") Long userId, @Param("isShared") Boolean isShared, @Param("shareToken") String shareToken, @Param("expiresAt") LocalDateTime expiresAt);

    /**
     * 更新锁的提醒设置
     */
    @Update("UPDATE final_answer_lock SET reminder_enabled = #{enabled}, reminder_time = #{reminderTime} WHERE id = #{lockId} AND user_id = #{userId}")
    void updateReminderSettings(@Param("lockId") Long lockId, @Param("userId") Long userId, @Param("enabled") Boolean enabled, @Param("reminderTime") LocalDateTime reminderTime);

    /**
     * 批量更新锁的状态
     */
    @Update("UPDATE final_answer_lock SET status = #{status} WHERE id IN (#{lockIds}) AND user_id = #{userId}")
    void batchUpdateStatus(@Param("lockIds") List<Long> lockIds, @Param("userId") Long userId, @Param("status") String status);
}